package com.hdoit.ares.logical.brokerage.dao.impl;

import java.util.List;

import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import com.hdoit.ares.logical.brokerage.dao.ICarShareBrokerageDao;
import com.hdoit.ares.logical.brokerage.model.CarBrokerageRecord;
import com.hdoit.ares.logical.brokerage.model.CarShareBrokerage;
import com.hdoit.ares.logical.cust.model.Customer;
import com.hdoit.ares.logical.viewmodel.brokerage.CarShareBrokerageSearch;
import com.hdoit.ares.logical.viewmodel.cust.CustomerSearch;
import com.tennetcn.common.dao.base.IFinderExpression;
import com.tennetcn.common.dao.base.impl.SuperDao;
import com.tennetcn.common.enums.OrderEnum;
import com.tennetcn.common.message.PagerModel;
import com.tennetcn.common.utils.CommonUtils;
import com.tennetcn.common.utils.FinderExpressionFactory;
/**
 * @author      yujinhui
 * @email       
 * @createtime  2017年12月14日 18:51:00
 * @comment
 */
@Component
public class CarShareBrokerageDaoImpl extends SuperDao<CarShareBrokerage> implements ICarShareBrokerageDao{

	@Override
	public List<CarShareBrokerage> queryList(CarShareBrokerageSearch search, PagerModel pagerModel) {
        IFinderExpression finderExpression=FinderExpressionFactory.createExpression();
		
		finderExpression.select("carShareBrokerage.*",
								"carBrokerageRecord.heisitateDate as hesitateDay",
								"carBrokerageRecord.bizInsureInfo as money",
								"carBrokerageRecord.taxPercent as shareMoney",
								"carBrokerageRecord.broPercent as proportion",
								"carBrokerageRecord.taskId as taskId",
								"carBrokerageRecord.bizInsureInfoPolicyNo as bizInsureInfoPolicyNo",
								"customer.username as customerName",
								"customer.level as customerLevel",
								"customer.userIdentity as userIdentity",
								"customer.refereeIdentity as refereeIdentity")
		                .from(CarShareBrokerage.class,"carShareBrokerage")
		                .leftJoin(Customer.class, "customer")
		                .on("customer.id","carShareBrokerage.customerId")
		                .leftJoin(CarBrokerageRecord.class, "carBrokerageRecord")
		                .on("carBrokerageRecord.id","carShareBrokerage.carBrokerageId")
		                .addOrder("carShareBrokerage.groupNum", OrderEnum.desc)
		                .addOrder("carShareBrokerage.sort", OrderEnum.asc)
		                .setMainTableAlias("carShareBrokerage");
		
		appendFinder(search,finderExpression);

        if(pagerModel == null){
            return queryList(finderExpression, search); 
          }
        return queryList(finderExpression, search, pagerModel);
	}

	private void appendFinder(CarShareBrokerageSearch search, IFinderExpression finder) {
        IFinderExpression finderExpression=FinderExpressionFactory.createExpression();
		
		finderExpression.select("bro.id")
			  			.from(CarBrokerageRecord.class,"bro")
			  			.leftJoin(Customer.class,"cus")
			  			.on("bro.userId","cus.id");
		
		if(!StringUtils.isEmpty(search.getConfirmState())){
			finder.andWhere("carShareBrokerage.confirmState like #{confirmState}")
				  .setParam("confirmState", "%"+search.getConfirmState()+"%");
		}
	
		if(!StringUtils.isEmpty(search.getCustomerName())){
			
			finderExpression.andWhere("cus.username like #{username}");
							
			finder.andWhere("carBrokerageRecord.id in("+finderExpression.toSql()+")")
				  .setParam("username", "%"+search.getCustomerName()+"%");
			
		}
		
		if(search.getStartMoney()!= null){
			finder.andWhere("carBrokerageRecord.taxPercent >= #{startMoney}")
				  .setParam("startMoney", search.getStartMoney());
		}
		if(search.getEndMoney()!= null){
			finder.andWhere("carBrokerageRecord.taxPercent <= #{endMoney}")
				  .setParam("endMoney", search.getEndMoney());
		}
		
		if(search.getStartDate()!=null){
			finder.andWhere("TIMESTAMPDIFF(SECOND,#{startDate},carShareBrokerage.generateDate)>=0")
				  .setParam("startDate", search.getStartDate());
		}
		if(search.getEndDate()!=null){
			finder.andWhere("TIMESTAMPDIFF(SECOND,#{endDate},carShareBrokerage.generateDate)<=0")
				  .setParam("endDate", search.getEndDate());
		}
		
		if(search.getUserIdentity() != null){
			
			finderExpression.andWhere("cus.userIdentity = #{userIdentity}");
			
			finder.andWhere("carBrokerageRecord.id in("+finderExpression.toSql()+")")
			  	  .setParam("userIdentity", search.getUserIdentity());
		}
		if(!StringUtils.isEmpty(search.getBizInsureInfoPolicyNo())){
			finder.andWhere("carBrokerageRecord.efcInsureInfoPolicyNo like #{efcInsureInfoPolicyNo}")
				  .setParam("efcInsureInfoPolicyNo", "%"+search.getBizInsureInfoPolicyNo()+"%");
		}
		if(!StringUtils.isEmpty(search.getTaskId())){
			finder.andWhere("carBrokerageRecord.taskId like #{taskId}")
				  .setParam("taskId", "%"+search.getTaskId()+"%");
		}
		
		if(!StringUtils.isEmpty(search.getCityId())){
			finder.andWhere("customer.cityId = #{cityId}")
			  	  .setParam("cityId", search.getCityId());
		}
		
		if(!StringUtils.isEmpty(search.getRefereeId())){
			IFinderExpression highMakerExpression=FinderExpressionFactory.createExpression();
			
			highMakerExpression.select("cs.carBrokerageId")
				  			   .from(CarShareBrokerage.class,"cs")
				  			   .andWhere("cs.customerId = #{refereeId}");
			
			finder.andWhere("carShareBrokerage.carBrokerageId in (" + highMakerExpression.toSql() + ")" )
				  .setParam("refereeId", search.getRefereeId());
		}
	}

	@Override
	public List<CarShareBrokerage> queryList(CarShareBrokerageSearch search) {
		return queryList(search,null);
	}

	@Override
	public int queryCount(CarShareBrokerageSearch search) {
        IFinderExpression finderExpression=FinderExpressionFactory.createExpression();
		
		finderExpression.select("count(DISTINCT carShareBrokerage.id) as count")
        				.from(CarShareBrokerage.class,"carShareBrokerage")
        				.leftJoin(Customer.class, "customer")
        				.on("customer.id","carShareBrokerage.customerId")
        				.leftJoin(CarBrokerageRecord.class, "carBrokerageRecord")
        				.on("carBrokerageRecord.id","carShareBrokerage.carBrokerageId")
        				.setMainTableAlias("carShareBrokerage");
		
		appendFinder(search,finderExpression);
		
		return queryCount(finderExpression, search);
	}

	@Override
	public int queryMaxGroupNum() {
        IFinderExpression finderExpression=FinderExpressionFactory.createExpression();
		
		finderExpression.select("max(groupNum) as m")
						.from(CarShareBrokerage.class);
		
		String scalar=queryScalar(finderExpression);
		
		if(StringUtils.isEmpty(scalar)){
			return 1;
		}
		
		return CommonUtils.getNumber(scalar) + 1;
	}

	@Override
	public List<CarShareBrokerage> queryListByBrokerageId(String brokerageId) {
        IFinderExpression finderExpression=FinderExpressionFactory.createExpression();
		
		finderExpression.select("carShareBrokerage.*","carBrokerageRecord.heisitateDate as hesitateDay","carBrokerageRecord.bizInsureInfo as money","carBrokerageRecord.taxPercent as shareMoney","carBrokerageRecord.broPercent as proportion","customer.username as customerName","customer.level as customerLevel","customer.userIdentity as userIdentity","customer.refereeIdentity as refereeIdentity")
		                .from(CarShareBrokerage.class,"carShareBrokerage")
		                .leftJoin(Customer.class, "customer")
		                .on("customer.id","carShareBrokerage.customerId")
		                .leftJoin(CarBrokerageRecord.class, "carBrokerageRecord")
		                .on("carBrokerageRecord.id","carShareBrokerage.carBrokerageId")
		                .andWhere("carShareBrokerage.carBrokerageId = #{brokerageId}")
		                .setParam("brokerageId", brokerageId)
		                .addOrder("carShareBrokerage.sort", OrderEnum.asc)
		                .setMainTableAlias("carShareBrokerage");
		
		return queryList(finderExpression);
	}

	@Override
	public List<CarShareBrokerage> queryCarShareBrokerageForStatistic(CustomerSearch search) {
		IFinderExpression finderExpression=FinderExpressionFactory.createExpression();
		
		finderExpression.select("carShareBrokerage.*","carBrokerageRecord.bizInsureInfo as money")
						.from(CarShareBrokerage.class,"carShareBrokerage")
						.leftJoin(CarBrokerageRecord.class,"carBrokerageRecord")
						.on("carBrokerageRecord.id","carShareBrokerage.carBrokerageId")
						.andWhere("DATE_FORMAT(carBrokerageRecord.`recordDate`, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )")
						.andWhere("carShareBrokerage.customerId = #{customerId}")
						.setParam("customerId", search.getId());
		
		
		return queryList(finderExpression);
	}
	
	
}